﻿CREATE PROCEDURE [dbo].[sp_Insert_Order]
	(
	@OrderID INT,
	@AccountID VARCHAR(10),
	@PeroidYear VARCHAR(4),
	@PeroidMonth VARCHAR(2),
	@ZipIDs VARCHAR(MAX)
	)
AS
	SET NOCOUNT ON
	
	BEGIN TRAN T1
	
	IF @OrderID = 0
	BEGIN
		-- Generate new order
		INSERT INTO Orders (AccountID, PeroidYear, PeroidMonth, IsPurchased, UpdateTS)
		VALUES (@AccountID, @PeroidYear, @PeroidMonth, 0, GETDATE());
		
		SET @OrderID=@@IDENTITY;
		
		IF (@ZipIDs <> '')
		BEGIN
			IF CHARINDEX(',', @ZipIDs) = 0
			BEGIN
				-- Insert new items for this order
				INSERT INTO OrderZip (OrderID, ZipID, Type)
				--VALUES 
				SELECT OrderID = @OrderID , substring(@ZipIDs, 1, charindex(';', @ZipIDs) - 1),
				substring(@ZipIDs, charindex(';', @ZipIDs) + 1, 1)
			END
			ELSE
			BEGIN
				-- Insert new items for this order
				INSERT INTO OrderZip (OrderID, ZipID, Type)
				--VALUES 
				SELECT OrderID=@OrderID, substring(subcolstr, 1, charindex(';', subcolstr) - 1),
				substring(subcolstr, charindex(';', subcolstr) + 1, 1) 
				FROM udf_CharlistToTable(@ZipIDs, ',');
			END
		END
	END
	ELSE
	BEGIN
		-- Update the order peroid
		UPDATE Orders SET PeroidYear=@PeroidYear, PeroidMonth=@PeroidMonth, UpdateTS = GETDATE()
		WHERE OrderID=@OrderID;
		
		-- Insert new items into the carts
		IF (@ZipIDs <> '')
		BEGIN
			IF CHARINDEX(',', @ZipIDs) = 0
			BEGIN
				-- Update agent name to null for deleted zips
				UPDATE ZipInfo SET AgentName = NULL
				WHERE ZipID <> substring(@ZipIDs, 1, charindex(';', @ZipIDs) - 1)
				AND ZipID IN (SELECT ZipID FROM OrderZip WHERE OrderID = @OrderID)
				
				DELETE FROM AssignmentDetail
				WHERE PeriodMonth = @PeroidMonth AND PeriodYear = @PeroidYear
				AND ZipID <> substring(@ZipIDs, 1, charindex(';', @ZipIDs) - 1)
				AND ZipID IN (SELECT ZipID FROM OrderZip WHERE OrderID = @OrderID)
				
				-- Remove all the old items in carts
				DELETE FROM OrderZip WHERE OrderID=@OrderID
				
				-- Insert new items for this order
				INSERT INTO OrderZip (OrderID, ZipID, Type)
				--VALUES 
				SELECT OrderID = @OrderID , substring(@ZipIDs, 1, charindex(';', @ZipIDs) - 1),
				substring(@ZipIDs, charindex(';', @ZipIDs) + 1, 1) 
			END
			ELSE
			BEGIN
				-- Update agent name to null for deleted zips
				UPDATE ZipInfo SET AgentName = NULL
				WHERE ZipID NOT IN (SELECT  substring(subcolstr, 1, charindex(';', subcolstr) - 1) FROM udf_CharlistToTable(@ZipIDs, ','))
				AND ZipID IN (SELECT ZipID FROM OrderZip WHERE OrderID = @OrderID)
				
				DELETE FROM AssignmentDetail
				WHERE PeriodMonth = @PeroidMonth AND PeriodYear = @PeroidYear
				AND ZipID NOT IN (SELECT  substring(subcolstr, 1, charindex(';', subcolstr) - 1) FROM udf_CharlistToTable(@ZipIDs, ','))
				AND ZipID IN (SELECT ZipID FROM OrderZip WHERE OrderID = @OrderID)
				
				-- Remove all the old items in carts
				DELETE FROM OrderZip WHERE OrderID=@OrderID
				
				-- Insert new items for this order
				INSERT INTO OrderZip (OrderID, ZipID, Type)
				--VALUES 
				SELECT OrderID=@OrderID, substring(subcolstr, 1, charindex(';', subcolstr) - 1),
				substring(subcolstr, charindex(';', subcolstr) + 1, 1) 
				FROM udf_CharlistToTable(@ZipIDs, ',');
			END
		END
		ELSE
		BEGIN
			UPDATE ZipInfo SET AgentName = NULL
			WHERE ZipID IN (SELECT ZipID FROM OrderZip WHERE OrderID = @OrderID)
			
			DELETE FROM AssignmentDetail
			WHERE PeriodMonth = @PeroidMonth AND PeriodYear = @PeroidYear
			AND ZipID IN (SELECT ZipID FROM OrderZip WHERE OrderID = @OrderID)
				
			-- Remove all the old items in carts
			DELETE FROM OrderZip WHERE OrderID=@OrderID
		END
	END
	
	IF ( @@ERROR <> 0 )
	BEGIN
		ROLLBACK TRAN T1
	END

	COMMIT TRAN T1
	
	RETURN
